﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public class WXnumberService
    {
       
        public void CreateTable(string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            try
            {
                string Name = "WXnumber" + UserName;
                string sql = "use bds249611391_db"
                    + " create table " + Name
                    + " ("
                    + " WXnumberId int not null identity(1,1),"
                    + " Number int not null"
                    + " )"
                    + " use bds249611391_db"
                    + " alter table " + Name
                    + " add constraint PK_WXnumberId primary key (WXnumberId)";
                ConnPoll.Open();
                SqlCommand com = new SqlCommand(sql, conn);
                com.ExecuteNonQuery();
                com = null;
                ConnPoll.Close();
            }
            catch (Exception)
            {

            }
        }

        /// <summary>
        /// 判断表是否存在
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public bool SearchTable(string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            bool isHave = false;
            string sql = "select * from WXnumber"+ UserName;
            ConnPoll.Open();
            SqlCommand com = new SqlCommand(sql,conn);
            SqlDataReader dr = com.ExecuteReader();
            while (dr.Read())
            {
                isHave = true;
            }
            dr.Close();
            com = null;
            ConnPoll.Close();
            return isHave;
        }


        public int Insert(WXnumber w, string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "insert into WXnumber"+ UserName + " (Number) values (@Number)";
            return SQLHelper.ExecuteNonQuery(conn,System.Data.CommandType.Text,sql,new SqlParameter("@Number",w.Number));
        }


        public int Delete( string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "delete WXnumber" + UserName;
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="w"></param>
        /// <param name="setStr">修改后的值</param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Set(WXnumber w,int setStr, string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "update WXnumber"+ UserName+ " set Number=@setStr where Number=@Number";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@setStr",setStr),
                new SqlParameter("@Number",w.Number)
            };
            return SQLHelper.ExecuteNonQuery(conn,System.Data.CommandType.Text,sql,pars);
        }


        public WXnumber SearchAll(string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            WXnumber w = new WXnumber();
            string sql = "select * from WXnumber"+ UserName;
            SqlDataReader dr = SQLHelper.ExecuteReader(conn,System.Data.CommandType.Text,sql);
            while (dr.Read())
            {
                w.WXnumberId = Convert.ToInt32(dr["WXnumberId"]);
                w.Number = Convert.ToInt32(dr["Number"]);
            }
            return w;
        }
    }
}
